package com.ai.view;

import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.ai.mmd.reviewed.vo.SbhzinfoVO;

/**
 * 导出特色航线Excel视图
 * 
 * @author Andy
 *
 */
public class QysbViewExcel extends AbstractExcelView {

	@SuppressWarnings("unchecked")
	@Override
	protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		
		String excelName = "年箱量报表";
		Object fromyear = model.get("fromyear");
		Object toyear = model.get("toyear");
		if (fromyear!=null&&toyear!=null) {
			if (fromyear.equals(toyear)) {
				excelName = fromyear.toString() + excelName;
			}else {
				excelName = fromyear.toString() + "-" + toyear.toString() + excelName;
			}
		}
		
		// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName+".xls", "UTF-8"));

		// 产生Excel表头
		HSSFSheet sheetwh = workbook.createSheet("箱量报表");

		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
		titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 11);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		titleStyle.setFont(font);
		
		//标题样式 start
		HSSFCellStyle headStyle = workbook.createCellStyle();
		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  

		HSSFFont titlefont = workbook.createFont();
		titlefont.setFontHeightInPoints((short) 22);
		headStyle.setFont(titlefont);
		//标题样式 end
		
		//单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  
		
		HSSFRow title = sheetwh.createRow(0);
		title.setHeight((short) 500);
		HSSFCell hsstitle = title.createCell(0);
		hsstitle.setCellValue(excelName);
		hsstitle.setCellStyle(headStyle);
		sheetwh.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

		HSSFRow headwh = sheetwh.createRow(1);
		headwh.setHeight((short) 400);
		
		for(int i=1;i<7;i++){
			sheetwh.setColumnWidth(i, 5000);
		}	
		
		HSSFCell h_whxl = headwh.createCell(0);
		h_whxl.setCellValue("序号");
		h_whxl.setCellStyle(titleStyle);
		
		HSSFCell h_whcmhc = headwh.createCell(1);
		h_whcmhc.setCellValue("企业名称");
		h_whcmhc.setCellStyle(titleStyle);

		HSSFCell h_whhydh = headwh.createCell(2);
		h_whhydh.setCellValue("申报年份");
		h_whhydh.setCellStyle(titleStyle);
		
		HSSFCell h_whxh = headwh.createCell(3);
		h_whxh.setCellValue("申报季度");
		h_whxh.setCellStyle(titleStyle);

		HSSFCell h_whfh = headwh.createCell(4);
		h_whfh.setCellValue("中转箱量(TEU)");
		h_whfh.setCellStyle(titleStyle);

		HSSFCell h_whsj = headwh.createCell(5);
		h_whsj.setCellValue("申请补贴金额(万元)");
		h_whsj.setCellStyle(titleStyle);

		List<SbhzinfoVO> gkwh = (List<SbhzinfoVO>) model.get("list");

		for (int i=2;i<=gkwh.size()+1;i++) {
			HSSFRow row = sheetwh.createRow(i);
			
			//序号
			HSSFCell r_xl = row.createCell(0);
			HSSFRichTextString richxl = new HSSFRichTextString(String.valueOf(i-1));
			r_xl.setCellValue(richxl);
			r_xl.setCellStyle(cellStyle);
			
			//企业名称
			HSSFCell r_cmhc = row.createCell(1);
			HSSFRichTextString richcmhc = new HSSFRichTextString(gkwh.get(i-2).getYhxxVO().getQyqc());
			r_cmhc.setCellValue(richcmhc);
			r_cmhc.setCellStyle(cellStyle);
			
			//申报年份
			HSSFCell r_hydh = row.createCell(2);
			HSSFRichTextString richhydh = new HSSFRichTextString(gkwh.get(i-2).getSbnf().toString());
			r_hydh.setCellValue(richhydh);
			r_hydh.setCellStyle(cellStyle);
			
			//申报季度
			HSSFCell r_xh = row.createCell(3);
			HSSFRichTextString richxh = new HSSFRichTextString(gkwh.get(i-2).getSbjd().toString());
			r_xh.setCellValue(richxh);
			r_xh.setCellStyle(cellStyle);
			
			//中转箱量
			HSSFCell r_fh = row.createCell(4);
			HSSFRichTextString richfh = new HSSFRichTextString(gkwh.get(i-2).getZxl().toString());
			r_fh.setCellValue(richfh);
			r_fh.setCellStyle(cellStyle);
			
			//补贴金额
			HSSFCell r_crmtsj = row.createCell(5);
			String cash = String.valueOf(Float.parseFloat(gkwh.get(i-2).getBtje().toString())/10000);
			HSSFRichTextString richcrmtsj = new HSSFRichTextString(cash);
			r_crmtsj.setCellValue(richcrmtsj);
			r_crmtsj.setCellStyle(cellStyle);
			
		}
		
		HSSFRow row = sheetwh.createRow(gkwh.size()+2);
		HSSFCell r_czrcell = row.createCell(4);
		r_czrcell.setCellValue("操作人:");
		HSSFCell r_czrvalue = row.createCell(5);
		r_czrvalue.setCellValue(model.get("name").toString());
	}
}
